<?php
echo "<h3>Welcome to installation of internship database</br></h3>";
installDatabase();
function installDatabase() {
    $host = 'localhost';
    $user = 'root';
    $password = '';
    $connected = mysql_connect($host, $user, $password);
    if (!$connected) {
        return mysql_error();
    }
        mysql_query("create database if not exists unhm_internships;") or die("Could not create database" . mysql_error());
        mysql_select_db("unhm_internships");

        mysql_query("create table ORGANIZATION(
organizationID          int             NOT NULL AUTO_INCREMENT PRIMARY KEY,
name                    varchar(45)     NOT NULL,
companyDescription      text            NULL,
address                 varchar(45)     NOT NULL,
city                    varchar(25)     NOT NULL,
state                   varchar(2)      NOT NULL,
zip                     varchar(10)	NOT NULL
);");

        mysql_query("create table CONTACTS(
contactID               int             NOT NULL AUTO_INCREMENT PRIMARY KEY,
organizationID          int             NOT NULL,
contactFirst            varchar(20)     NOT NULL,
contactLast		varchar(20)     NOT NULL,
contactEmail            varchar(50)	NULL,
contactPhone            varchar(13)	NULL,
contactExt              varchar(4)      NULL,
contactFax		varchar(13)     NULL
);");
       mysql_query("create table INTERNSHIP_OPPORTUNITY(
opportunityID		int	NOT NULL AUTO_INCREMENT PRIMARY KEY,
organizationID		int		NOT NULL,
title			varchar(25)	NOT NULL,
numPositionsAvailable	int		NOT NULL,
semester		varchar(15)	NOT NULL,
payRate			decimal(3,2)	NOT NULL,
duration		varchar(15)	NOT NULL,
responsibilities	text		NOT NULL,
qualifications		text		NULL,
instructions		text		NULL,
comments text NULL,
dateAdded		date		NOT NULL
);");
       mysql_query("create table INTERNSHIP(
internshipID            int		NOT NULL AUTO_INCREMENT PRIMARY KEY,
opportunityID           int		NOT NULL,
studentID		int		NOT NULL,
gotHired		varchar(25)	NOT NULL,
hours			varchar(25)	NOT NULL,
special			varchar(3)	NOT NULL,
semester		varchar(10)	NOT NULL
);");
       mysql_query("create table STUDENT(
studentID		int		NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstName		varchar(25)  	NOT NULL,
lastName		varchar(25)	NOT NULL,
username		varchar(10)	NOT NULL,
currentGPA		decimal(2,2)	NOT NULL,
major			text		NOT NULL,
yearStatus		text		NULL,
dateModified            date		NOT NULL
);");
       mysql_query("ALTER TABLE INTERNSHIP_OPPORTUNITY ADD CONSTRAINT FOREIGN KEY intern_op_fk (organizationID)
	REFERENCES ORGANIZATION (organizationID);");
       mysql_query("ALTER TABLE INTERNSHIP ADD CONSTRAINT FOREIGN KEY internship_fk1 (opportunityID)
	REFERENCES INTERNSHIP_OPPORTUNITY (opportunityID);");
       mysql_query("ALTER TABLE INTERNSHIP ADD CONSTRAINT FOREIGN KEY internship_fk2 (studentID)
	REFERENCES STUDENT (studentID);");
       mysql_query("ALTER TABLE CONTACTS ADD CONSTRAINT FOREIGN KEY contact_fk (organizationID)
	REFERENCES ORGANIZATION (organizationID);");
       mysql_query("create table USER(
username		varchar(25)	NOT NULL,
password		varchar(32)	NOT NULL,
firstName		varchar(25)	NOT NULL,
lastName		varchar(25)	NOT NULL,
permissionLevel         text		NOT NULL
);");
    }
?>
